﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Text.RegularExpressions;
using System.Web;
using System.Data;

public class SQLM : IDisposable
{
    private SqlConnection conn;
    private SqlCommand cmd;
    private SqlTransaction tran;
    private SqlDataReader dr;
    private SqlDataAdapter da;
    public string ConnectionString;

    public SQLM()
        : this("")
    { }
    public SQLM(string connectionString)
    {
        Open(connectionString);
    }

    public void ReOpen(string connectionString = "")
    {
        Dispose();
        Open(connectionString);
    }

    private void Open(string connectionString)
    {
        if (!string.IsNullOrEmpty(connectionString))
            ConnectionString = connectionString;
        else
            ConnectionString = this.GetConnectionString();

        conn = new SqlConnection();
        conn.ConnectionString = ConnectionString;
        conn.Open();
        cmd = conn.CreateCommand();
    }

    public void SetTimeOut(int timeout)
    {
        cmd.CommandTimeout = timeout;
    }

    public void BeginTransaction()
    {
        try
        {
            tran = conn.BeginTransaction();
            cmd.Transaction = tran;
        }
        catch (Exception ex)
        {
            Dispose();
            throw new Exception("BeginTransaction 错误：" + ex.Message);
        }
    }
    public void Commit()
    {
        try
        {
            tran.Commit();
            tran.Dispose();
        }
        catch (Exception ex)
        {
            Dispose();
            throw new Exception("Commit 错误：" + ex.Message);
        }
    }
    public void Rollback()
    {
        try
        {
            tran.Rollback();
            tran.Dispose();
        }
        catch (Exception ex)
        {
            Dispose();
            throw new Exception("Rollback 错误：" + ex.Message);
        }
    }

    public int ExecuteNonQuery(string sql, params object[] s)
    {
        return ExecuteNonQuery(sql, null, s);
    }
    public int ExecuteNonQuery(string sql, string[] p, params object[] s)
    {
        SqlCmd(sql, p, s);
        int result = 0;
        try
        {
            result = cmd.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            showError(ex, sql, p, s);
        }

        return result;
    }

    public object ExecuteScalar(string sql, params object[] s)
    {
        return ExecuteScalar(sql, null, s);
    }
    public object ExecuteScalar(string sql, string[] p, params object[] s)
    {
        SqlCmd(sql, p, s);
        object result = null;
        try
        {
            result = cmd.ExecuteScalar();
        }
        catch (Exception ex)
        {
            showError(ex, sql, p, s);
        }

        return result;
    }

    public SqlDataReader ExecReader(string sql, params object[] s)
    {
        return ExecReader(sql, null, s);
    }
    public SqlDataReader ExecReader(string sql, string[] p, params object[] s)
    {
        SqlCmd(sql, p, s);
        dr = cmd.ExecuteReader();

        return dr;
    }

    public DataRow ExecDr(string sql, params object[] s)
    {
        return ExecDr(sql, null, s);
    }
    public DataRow ExecDr(string sql, string[] p, params object[] s)
    {
        SqlCmd(sql, p, s);
        DataSet ds = new DataSet();
        da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            showError(ex, sql, p, s);
        }
        DataRow dr = null;
        try
        {
            dr = ds.Tables[0].Rows[0];
        }
        catch { }

        return dr;
    }

    public DataSet ExecDs(string sql, params object[] s)
    {
        return ExecDs(sql, null, s);
    }
    public DataSet ExecDs(string sql, string[] p, params object[] s)
    {
        SqlCmd(sql, p, s);
        DataSet ds = new DataSet();
        da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            showError(ex, sql, p, s);
        }

        return ds;
    }

    public DataTable ExecDt(string sql, params object[] s)
    {
        return ExecDt(sql, null, s);
    }
    public DataTable ExecDt(string sql, string[] p, params object[] s)
    {
        SqlCmd(sql, p, s);
        DataSet ds = new DataSet();
        da = new SqlDataAdapter();
        da.SelectCommand = cmd;
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            showError(ex, sql, p, s);
        }

        try
        {
            return ds.Tables[0];
        }
        catch
        {
            return null;
        }
    }

    public DataSet ExecProc(string procedureName)
    {
        return ExecProc(procedureName, null, null);
    }
    public DataSet ExecProc(string procedureName, string[] paramsName, object[] value)
    {
        cmd.Parameters.Clear();
        cmd.CommandText = procedureName;
        cmd.CommandType = CommandType.StoredProcedure;

        DataSet ds = new DataSet();
        da = new SqlDataAdapter();
        if (paramsName != null)
        {
            for (int i = 0; i < paramsName.Length; i++)
                cmd.Parameters.AddWithValue(paramsName[i], value[i]);
        }
        da.SelectCommand = cmd;
        try
        {
            da.Fill(ds);
        }
        catch (Exception ex)
        {
            showError(ex, procedureName, paramsName, value);
        }

        return ds;
    }

    public void Dispose()
    {
        if (tran != null)
            tran.Dispose();
        if (dr != null)
        {
            dr.Close();
            dr.Dispose();
        }
        if (da != null)
            da.Dispose();
        if (cmd != null)
            cmd.Dispose();
        if (conn != null)
        {
            conn.Close();
            conn.Dispose();
        }
    }

    public void showError(Exception ex, string sql, string[] p, object[] s)
    {
        Dispose();

        string msg = ex.Message + "\r\n\r\nSQL(ProcedureName)：\r\n" + sql;
        if (p != null) msg += "\r\n\r\nParams：\r\n" + string.Join(",", p);
        if (s != null) msg += "\r\n\r\nValues：\r\n" + string.Join("\r\n", Array.ConvertAll(s, new Converter<object, string>(c => c.ToStr())));
        throw new Exception(msg);
    }

    private void SqlCmd(string sql, string[] p, params object[] s)
    {
        if (dr != null)
        {
            dr.Close();
            dr.Dispose();
        }

        cmd.Parameters.Clear();
        cmd.CommandText = sql;
        cmd.CommandType = CommandType.Text;

        if (s == null) return;

        if (p == null)
        {
            Regex re = new Regex(@"[^@](@[^\s,]+)", RegexOptions.IgnoreCase);
            Match m = re.Match(sql);
            int i = 0;
            List<string> str = new List<string>();
            while (m.Success)
            {
                if (i == s.Length) break;
                if (!str.Contains(m.Groups[1].Value))
                {
                    cmd.Parameters.AddWithValue(m.Groups[1].Value, s[i]);
                    str.Add(m.Groups[1].Value);
                    i++;
                }
                m = m.NextMatch();
            }
        }
        else
        {
            for (int i = 0; i < p.Length; i++)
            {
                if (i == s.Length) break;
                cmd.Parameters.AddWithValue(p[i], s[i]);
            }
        }
    }
}
